# -*- coding=utf-8 -*-
'''
Created on 2012-4-23

@author: Administrator
'''
import sqlite3,os
class DB(object):
    '''
    sqlite date base
    '''
    datas=None


    def __init__(self,dbPath="./data.db"):
        '''
        Constructor
        '''
        self.classes=None
        flag = False
        if os.path.exists(dbPath):
            flag = True
        
        self.cx = sqlite3.connect(dbPath)
        if flag == False:
            self.init_db()
    
    def init_db(self):
        """
        init db
        """
        cu=self.cx.cursor()
        cu.execute("""create table class(id INTEGER PRIMARY KEY AUTOINCREMENT,pid INTEGER default 1,name VARCHAR(60));""")
        cu.executemany("""insert into class Values(NULL,1,?);""",[(u"分类",),(u"计算机",),(u"数学",)])
        cu.execute("""create table data(id INTEGER PRIMARY KEY AUTOINCREMENT,classid INTEGER NOT NULL,name VARCHAR(60),data TEXT)""")
        cu.execute("""insert into data Values(null,2,?,?)""",('test1','wedasdsadasdsd'))
        cu.execute("""insert into data Values(null,2,?,?)""",('test2','sadasdsd'))
        self.cx.commit()
    
    def get_classes(self):
        if self.classes== None:
            self.get_classes_from_db()
        return self.classes
    
    def get_classes_from_db(self):
        cu=self.cx.cursor()
        cu.execute("""select *from class where id !=1""")
        self.classes = cu.fetchall()
        cu.close()
            
    def append_class(self,name):
        cu=self.cx.cursor()
        cu.execute("""insert into class Values(NULL,1,'%s');""" %(name))
        cu.execute("""select * from class where id=last_insert_rowid();""" )
        self.cx.commit()
        newclass=cu.fetchone()
        cu.close()
        print newclass
        self.classes+=newclass
        return newclass[0]
    
    def del_data_by_classid(self,classid):
        self.datas=filter(lambda n:n[2]!=classid,self.datas)
        self.del_data_by_classid_db(classid)
    
    def append_data(self,classid,name,text=""):
        cu=self.cx.cursor()
        cu.execute("""insert into data Values(NULL,?,?,?);""",(classid,name,text))
        cu.execute("""select * from data where id=last_insert_rowid();""")
        self.cx.commit()
        newdata=cu.fetch()
        self.datas+=newdata
        cu.close()
        return newdata[0]
        
  
    def del_data_by_classid_db(self,classid):
        cu=self.cx.cursor()
        cu.execute("""delete from data where classid=%d;""" %(classid))
        self.cx.commit()
        cu.close()
    
    def del_class_db(self,classid):
        cu=self.cx.cursor()
        cu.execute("""delete from class where id=%d"""%(classid))
        self.cx.commit()
        cu.close()
               
    def del_class(self,name):
        for i in xrange(len(self.classes)):
            if self.classes[i][2]==name:
                self.del_data_by_classid(self.classes[i][1])
                self.del_class_db(i)
                del self.classes[i]
                break
           
    def get_datas(self):
        if self.datas == None:
            self.get_datas_from_db()
        return self.datas
        
    def get_datas_from_db(self):
        cu=self.cx.cursor()
        cu.execute("select * from data")
        self.datas=cu.fetchall()
        cu.close()
               
if __name__=='__main__':
    db=DB()